package org.multimedia.jolitorask.asterisk;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

@SuppressWarnings("unchecked")
public class AsteriskWrapperSession implements SessionBean{

	private static final long serialVersionUID = 1L;
	@SuppressWarnings("all")
	private SessionContext context;
	
	/* Parametre */
	private String compteClient;
	private String loginUtilisateur;
	
	/* Connexion */
	private static Connection connexionBD;
	private String dbName = "jdbc_asterisk";
	
	/* Requetes */
	private static String reqNbPhone = "SELECT name FROM `sip_buddies` WHERE name LIKE ?";
	private static String reqNextExten = "SELECT (MAX(CAST(exten AS SIGNED))+1) AS exten FROM `sip_extensions` WHERE context=?";
	private static String reqIdBuddy = "SELECT id FROM `sip_buddies` WHERE name = ?";
	private static String reqMailBox = "SELECT exten FROM utilisateur2buddy JOIN sip_buddies ON utilisateur2buddy.id_buddy = sip_buddies.id WHERE sip_buddies.name = ?";
	
	/* Insertions */
	private static String insExten = "INSERT INTO `sip_extensions` ( `id` , `context` , `exten` , `priority` , `app` , `appdata` ) VALUES (NULL , ?, ?, ?, ?, ?)";
	private static String insVoiceMail = "INSERT INTO `sip_voicemail` ( `uniqueid` , `customer_id` , `context` , `mailbox` , `password` , `fullname` , `email` , `pager` , `stamp` ) VALUES (NULL , '0', ?, ?, '0000', ?, ?, '', NOW())";
	private static String insBuddy = "INSERT INTO sip_buddies (`name`, `context`, `callerid`, `mailbox`, `secret`, `username`, `host`) VALUES (?, ?, ?, ?, ?, ?, 'dynamic')";
	
	/* Delete */
	private static String delBuddy = "DELETE FROM `sip_buddies` WHERE id = ?";
	private static String delExten = "DELETE FROM `sip_extensions` WHERE context = ? AND exten = ?";
	private static String delVoiceMail = "DELETE FROM `sip_voicemail` WHERE context = ? AND mailbox = ?";
	
	/* Tables correspondance */
	private static String insU2B = "INSERT INTO utilisateur2buddy (`id_utilisateur`, `id_buddy`, `exten`) VALUES (?, ?, ?)";
	private static String insT2S = "INSERT INTO `telephone2sip` (`id_telephone` , `id_buddy` , `exten` , `mailbox`)	VALUES (?, ?, ?, ?)";
	private static String reqLineT2S = "SELECT * FROM `telephone2sip` WHERE id_telephone = ?";
	private static String upBuddy = "UPDATE `sip_buddies` SET `secret`= ? WHERE context = ? AND name = ?";
	private static String delLineT2S = "DELETE FROM `telephone2sip` WHERE id_telephone = ?";
	private static String delLineU2B = "DELETE FROM `utilisateur2buddy` WHERE id_utilisateur = ?";
	private static String delLineB2U = "DELETE FROM `utilisateur2buddy` WHERE id_buddy = ?";
	private static String reqLineU2B = "SELECT * FROM `utilisateur2buddy` WHERE id_utilisateur = ?";
	private static String reqLineT2SByMailBox = "SELECT id_telephone FROM `telephone2sip` WHERE mailbox = ?";
	private static String reqBuddyFromUtilisateur = "SELECT exten FROM `utilisateur2buddy` WHERE id_utilisateur = ?";
	private static String reqNameBuddyFromIdUtilisateur = "SELECT name FROM utilisateur2buddy JOIN sip_buddies ON utilisateur2buddy.id_buddy = sip_buddies.id WHERE utilisateur2buddy.id_utilisateur  = ?";
	private static String reqMailBoxFromIdUtilisateur = "SELECT exten FROM utilisateur2buddy JOIN sip_buddies ON utilisateur2buddy.id_buddy = sip_buddies.id WHERE utilisateur2buddy.id_utilisateur  = ?";
	private static String reqNameBuddyFromIdTelephone = "SELECT name FROM telephone2sip JOIN sip_buddies ON telephone2sip.id_buddy = sip_buddies.id WHERE telephone2sip.id_telephone  = ?";
	
	
	/* Updates */
	private static String getIdSuperBuddyAndMailBoxNumber = "SELECT utilisateur2buddy.id_buddy AS id, utilisateur2buddy.exten FROM sip_buddies JOIN utilisateur2buddy ON sip_buddies.id = utilisateur2buddy.id_buddy WHERE sip_buddies.name = ?";
	private static String getListPhoneFromMailBox = "SELECT exten, id_buddy FROM telephone2sip WHERE mailbox = ?";
	private static String upCallerId = "UPDATE sip_buddies SET callerid = ? WHERE id = ?";
	private static String upVoiceMailName = "UPDATE sip_voicemail SET fullname = ? WHERE context = ? AND mailbox = ?";
	private static String upVoiceMail = "UPDATE `sip_voicemail` SET `password`= ?, `email`= ? WHERE context = ? AND mailbox = ?";
	
	public void ejbCreate() throws CreateException{
		
		InitialContext ic;
		try {
			
			ic = new InitialContext();
			DataSource ds = (DataSource) ic.lookup(dbName);
			connexionBD = ds.getConnection();
			
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	/*******************************/
	/** Fonctions our l'EJBObject **/
	/*******************************/
	
	public void init(String compte, String login){
		
		compteClient = compte;
		loginUtilisateur = login;
	}
	
	
	public int addSuperBuddy(int id_utilisateur, String username, String userfirstname, String userpass, String mail) throws SQLException{
		
		connexionBD.setAutoCommit(false);
		
		String buddy = compteClient+"_"+loginUtilisateur;
		int numero = getNextExtention(compteClient);
		
		insertMailBox(compteClient, numero+"", 1, username+" "+userfirstname, mail);

		insertBuddy(compteClient, buddy, userpass, username+" "+userfirstname+" <"+numero+">", numero);
		
		int id_buddy = findBuddyByName(buddy);
		insertUtilisateur2Buddy(id_utilisateur, id_buddy, numero);
		
		connexionBD.commit();
		connexionBD.setAutoCommit(true);
		
		return numero;
	}
	
	

	public int addNewPhone(int id_utilisateur, int id_telephone, String username, String userfirstname, String userpass, String mail) throws SQLException{
		
		connexionBD.setAutoCommit(false);
		
		String super_buddy = compteClient+"_"+loginUtilisateur;
		String buddy = super_buddy+"_phone"+getNumberNextPhone(super_buddy+"_phone");
		int mailbox = findMailBoxByName(super_buddy);
		int numero = getNextExtention(compteClient);
		
		insertBuddy(compteClient, buddy, userpass, username+" "+userfirstname+" <"+numero+">", mailbox);
		insertDial(compteClient, numero+"", 1, buddy);
		insertExtension(compteClient, numero+"", 3, "VoiceMail", mailbox+"@"+compteClient);
		int id_buddy = findBuddyByName(buddy);
		insertUtilisateur2Buddy(id_utilisateur, id_buddy, numero);
		insertTelephone2Sip(id_telephone, id_buddy, numero, mailbox);
		
		
		connexionBD.commit();
		connexionBD.setAutoCommit(true);
		
		return numero;
	}
	
	public void updateVoiceMail(int id_utilisateur, String password, String email) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqMailBoxFromIdUtilisateur);
		stmt.setInt(1, id_utilisateur);
		ResultSet rs = stmt.executeQuery();
		
		if(!rs.next())
			throw new SQLException("Buddy not found");
		
		stmt = connexionBD.prepareStatement(upVoiceMail);
		stmt.setString(1, password);
		stmt.setString(2, email);
		stmt.setString(3, compteClient);
		stmt.setString(4, rs.getString("exten"));
		stmt.execute();
		
		stmt.close();
	}
	
	/**
	 * Met a jour le callerid en fonction du nouveau nom et du nouveau prenom
	 * @param newname		Nouveau Nom
	 * @param newfirstname	Nouveau Prenom
	 * @throws SQLException
	 */
	public void updateCallerId(String newname, String newfirstname )throws SQLException{
		
		String callerid = newname+" "+newfirstname+" ";
		
		PreparedStatement stmt = connexionBD.prepareStatement(getIdSuperBuddyAndMailBoxNumber);
		stmt.setString(1, compteClient+"_"+loginUtilisateur);
		ResultSet rs = stmt.executeQuery();
		
		String mailbox;
		
		if( rs.next() ){
			
			int id_super = rs.getInt("id");
			mailbox = rs.getString("exten");
			
			stmt = connexionBD.prepareStatement(getListPhoneFromMailBox);
			stmt.setString(1, mailbox);
			ResultSet liste = stmt.executeQuery();
			
			while(liste.next()){
				
				stmt = connexionBD.prepareStatement(upCallerId);
				stmt.setString(1, callerid+"<"+liste.getString("exten")+">");
				stmt.setInt(2, liste.getInt("id_buddy"));
				stmt.execute();
			}
			
			stmt = connexionBD.prepareStatement(upCallerId);
			stmt.setString(1, callerid+"<"+mailbox+">");
			stmt.setInt(2, id_super);
			stmt.execute();
			
			stmt = connexionBD.prepareStatement(upVoiceMailName);
			stmt.setString(1, newname+" "+newfirstname);
			stmt.setString(2, compteClient);
			stmt.setString(3, mailbox);
			stmt.execute();
		}
		
	}
	
	public void updateSuperBuddy( int id_utilisateur, String password) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqNameBuddyFromIdUtilisateur);
		stmt.setInt(1, id_utilisateur);
		ResultSet rs = stmt.executeQuery();
		
		if(!rs.next())
			throw new SQLException("Buddy not found");
		
		stmt = connexionBD.prepareStatement(upBuddy);
		stmt.setString(1, password);
		stmt.setString(2, compteClient);
		stmt.setString(3, rs.getString("name"));
		stmt.execute();
		
		stmt.close();
	}
	
	public void updatePhoneBuddy(int id_telephone, String password) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqNameBuddyFromIdTelephone);
		stmt.setInt(1, id_telephone);
		ResultSet rs = stmt.executeQuery();
		
		if(!rs.next())
			throw new SQLException("Buddy not found");
		
		stmt = connexionBD.prepareStatement(upBuddy);
		stmt.setString(1, password);
		stmt.setString(2, compteClient);
		stmt.setString(3, rs.getString("name"));
		stmt.execute();
		
		stmt.close();
	}
	
	public void deleteUser(int id_utilisateur)throws SQLException{
		
		connexionBD.setAutoCommit(false);
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqBuddyFromUtilisateur); 
		stmt.setInt(1, id_utilisateur);
		ResultSet rs = stmt.executeQuery();
		
		if( !rs.next() )
			return;
		
		stmt = connexionBD.prepareStatement(reqLineT2SByMailBox);
		stmt.setInt(1, rs.getInt("exten"));
		rs = stmt.executeQuery();
		
		int id_tel;
		while(rs.next()){
			
			id_tel = rs.getInt("id_telephone");
			privateDeletePhone(id_tel);
		}
		
		
		 stmt = connexionBD.prepareStatement(reqLineU2B);
		 stmt.setInt(1, id_utilisateur);
		 rs = stmt.executeQuery();
		 
		 if( rs.next() ){
			 
			deleteBuddy(rs.getInt("id_buddy"));
			deleteExtensions(compteClient, rs.getString("exten"));
			deleteVoiceMail(compteClient, rs.getString("exten"));
		 }

		 deleteLineU2B(id_utilisateur);
		 
		 stmt.close();
		 
		 connexionBD.commit();
		 connexionBD.setAutoCommit(true);
	}
	
	
	public void deletePhone(int id_telephone)throws SQLException{
		
		connexionBD.setAutoCommit(false);
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqLineT2S);
		stmt.setInt(1, id_telephone);
		int id_b;
		
		ResultSet rs = stmt.executeQuery();
		while( rs.next()){
			
			id_b = rs.getInt("id_buddy");
			
			deleteBuddy(id_b);
			deleteLineB2U(id_b);
			deleteExtensions(compteClient, rs.getString("exten"));
		}
		
		deleteLineT2S(id_telephone);
		
		stmt.close();
		
		connexionBD.commit();
		connexionBD.setAutoCommit(true);
	}
	


	/*****************************/
	/**      Fonctions pour     **/ 
	/**   traitements internes  **/
	/*****************************/
	


	/**
	 * 
	 * @param account		Le compte jolitorask
	 * @return				Le numero du prochain numero a disponible pour ce compte		
	 * @throws SQLException
	 */
	private int getNextExtention(String account) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqNextExten);
		stmt.setString(1, account);
		
		ResultSet rs = stmt.executeQuery();
		
		if(rs.next()){
			int exten = rs.getInt("exten");
			if( exten == 0 )
				return 1000;
			
			stmt.close();
			return exten;
		}
		
		throw new SQLException("Internal Error");
	}
	
	
	/**
	 * Insere une ligne dans sip_buddies
	 * @param account		compte client jolitorask
	 * @param name			login pour asterisk
	 * @param secret		mot de passe
	 * @param callerid		caller id ! et oui !
	 * @param numMailbox	numero associe
	 * @throws SQLException
	 */
	private void insertBuddy(String account, String name, String secret, String callerid, int numMailbox) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(insBuddy);
		stmt.setString(1, name);
		stmt.setString(2, account);
		stmt.setString(3, callerid);
		stmt.setString(4, numMailbox+"@"+account);
		stmt.setString(5, secret);
		stmt.setString(6, name);
		
		stmt.execute();
		stmt.close();
	}
	
	/**
	 * 
	 * @param id_u			identifiant dans la table `utilisateurs` (J2EE)
	 * @param id_b			identifiant dans la table `sip_buddies` (Asterisk)
	 * @param exten			numero de la mailbox commune
	 * @throws SQLException
	 */
	private void insertUtilisateur2Buddy(int id_u, int id_b, int exten) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(insU2B);
		stmt.setInt(1, id_u);
		stmt.setInt(2, id_b);
		stmt.setString(3, exten+"");
		
		stmt.execute();
		stmt.close();
	}
	
	
	private void insertTelephone2Sip(int id_t, int id_b, int exten, int mailbox) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(insT2S);
		stmt.setInt(1, id_t);
		stmt.setInt(2, id_b);
		stmt.setString(3, exten+"");
		stmt.setString(4, mailbox+"");
		
		stmt.execute();
		stmt.close();
	}
	
	
	private int getNumberNextPhone(String name) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqNbPhone);
		stmt.setString(1, name+"%");
		ResultSet rs = stmt.executeQuery();
		String n;
		int max = 0, tmp, size = name.length();
		
			
		while(rs.next()){
			
			n = rs.getString("name");
			tmp = Integer.parseInt(n.substring(size));
			System.out.println(tmp);
			if(tmp>max)
				max = tmp;
			
		}
		stmt.close();
		return max+1;
	}
	
	
	/**
	 * 
	 * @param name			nom du buddy dans `sip_buddies`
	 * @return				l'identifiant du buddy trouve
	 * @throws SQLException
	 */
	private int findBuddyByName(String name) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqIdBuddy);
		stmt.setString(1, name);
		
		ResultSet rs = stmt.executeQuery();
		
		if(rs.next()){
			stmt.close();
			return rs.getInt("id");
		}
		else throw new SQLException("Buddy not found : "+name);
	}

	private int findMailBoxByName(String name)throws SQLException{
		
		
		PreparedStatement stmt = connexionBD.prepareStatement(reqMailBox);
		stmt.setString(1, name);
		
		ResultSet rs = stmt.executeQuery();
		
		if(rs.next()){
			stmt.close();
			return rs.getInt("exten");
		}
		else throw new SQLException("Buddy not found : "+name);
	}
	
	
	private void insertVoiceMail( String context, String exten, String name, String mail ) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(insVoiceMail);
		stmt.setString(1, context);
		stmt.setString(2, exten);
		stmt.setString(3, name);
		stmt.setString(4, mail);
		
		stmt.execute();
		stmt.close();
	}
	
	private void insertExtension( String context, String exten, int priority, String app, String appdata ) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(insExten);
		stmt.setString(1, context);
		stmt.setString(2, exten);
		stmt.setInt(3, priority);
		stmt.setString(4, app);
		stmt.setString(5, appdata);
		
		stmt.execute();
		stmt.close();
	}

	private void insertMailBox(String context, String exten, int priority, String name, String mail) throws SQLException{
		
		insertExtension(context, exten, priority, "VoiceMail", exten+"@"+context);
		insertVoiceMail(context, exten, name, mail);		
	}

	private void insertDial(String context, String exten, int priority, String buddy) throws SQLException{
		
		insertExtension(context, exten, priority, "SetMusicOnHold", "default");
		insertExtension(context, exten, priority+1, "Dial", "SIP/"+buddy+"|60|m");
	}

	private void deleteBuddy(int id_buddy) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(delBuddy);
		stmt.setInt(1, id_buddy);
		stmt.execute();
		stmt.close();
	}

	
	private void deleteLineU2B(int id_utilisateur) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(delLineU2B);
		stmt.setInt(1, id_utilisateur);
		stmt.execute();
		stmt.close();
	}
	
	private void deleteLineB2U(int id_buddy) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(delLineB2U);
		stmt.setInt(1, id_buddy);
		stmt.execute();
		stmt.close();
	}
	

	private void deleteLineT2S(int id_telephone) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(delLineT2S);
		stmt.setInt(1, id_telephone);
		stmt.execute();
		stmt.close();
	}


	private void deleteExtensions(String compteClient, String exten) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(delExten);
		stmt.setString(1, compteClient);
		stmt.setString(2, exten);
		stmt.execute();
		stmt.close();
	}
	
	private void deleteVoiceMail(String compteClient, String exten) throws SQLException{
		
		PreparedStatement stmt = connexionBD.prepareStatement(delVoiceMail);
		stmt.setString(1, compteClient);
		stmt.setString(2, exten);
		stmt.execute();
		stmt.close();
	}
	

	private void privateDeletePhone(int id_telephone)throws SQLException{
			
		PreparedStatement stmt = connexionBD.prepareStatement(reqLineT2S);
		stmt.setInt(1, id_telephone);
		int id_b;
		
		ResultSet rs = stmt.executeQuery();
		while( rs.next()){
			
			id_b = rs.getInt("id_buddy");
			
			deleteBuddy(id_b);
			deleteLineB2U(id_b);
			deleteExtensions(compteClient, rs.getString("exten"));
		}
		
		deleteLineT2S(id_telephone);
		
		stmt.close();
		
	}
	/************************/
	/** Fonctions Heritees **/
	/**    Non utilisees   **/
	/************************/
	public void ejbActivate() throws EJBException, RemoteException{}
	public void ejbPassivate() throws EJBException, RemoteException{}
	public void ejbRemove() throws EJBException, RemoteException{}
	public void setSessionContext(SessionContext sc) throws EJBException, RemoteException {
		context = sc;
	}
	public void unsetEntityContext() throws EJBException, RemoteException {
		context = null;
	}
	
}
